Improve ODBC Throughput ?

Hi list !

I mostly use my PostgreSQL as a backend DB with Access2000
frontends.
The tables are linked via psqlODBC.
The problem I have is that even though queries / views run
very fast, the data take a long time to arrive to the client.
In the best situations, data is sent/received at ~500kB per
second (for instance, tables with only one column, which is
a very large text field >10.000 bytes per field).
In bad situations, I am at ~50kB per second.
If the queries / views return large resultsets, it can
seconds for the table to open., even though the query ran in
milliseconds on the server (explain analyze proves this).

What can I do to improve this throughput ? Either on the
server side, or on the ODBC client side ?

As a side note : network congestion, server acitvity is not
a good explanation. This server also is a file server, and I
transfer files at 10MB per second all the time.

Thanks for your advices !
--
Arnaud

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: Improve ODBC Throughput ?

What kinds of indexes do you have on your tables? I think the way=0D
Access works to limit transfer it first pulls the indexes and probably=0D
the primary one first to determine how to fetch and how many to fetch.=0D
I suspect if you have no indexes performance would be really bad. =0D
=0D
Also which version of psqlODBC driver are you using?=0D
=0D
-----Original Message-----=0D
From: pgsql-odbc-owner@postgresql.org=0D
[mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Arnaud Lesauvage=0D
Sent: Thursday, October 19, 2006 8:55 AM=0D
To: pgsql-odbc@postgresql.org=0D
Subject: [ODBC] Improve ODBC Throughput ?=0D
=0D
Hi list !=0D
=0D
I mostly use my PostgreSQL as a backend DB with Access2000 frontends.=0D
The tables are linked via psqlODBC.=0D
The problem I have is that even though queries / views run very fast,=0D
the data take a long time to arrive to the client.=0D
In the best situations, data is sent/received at ~500kB per second (for=0D
instance, tables with only one column, which is a very large text field=0D
>10.000 bytes per field).=0D
In bad situations, I am at ~50kB per second.=0D
If the queries / views return large resultsets, it can seconds for the=0D
table to open., even though the query ran in milliseconds on the server=0D
(explain analyze proves this).=0D
=0D
What can I do to improve this throughput ? Either on the server side, or=0D
on the ODBC client side ?=0D
=0D
As a side note : network congestion, server acitvity is not a good=0D
explanation. This server also is a file server, and I transfer files at=0D
10MB per second all the time.=0D
=0D
Thanks for your advices !=0D
--=0D
Arnaud=0D
=0D
---------------------------(end of broadcast)---------------------------=0D
TIP 2: Don't 'kill -9' the postmaster=0D
=0D
-----------------------------------------=0D
The substance of this message, including any attachments, may be=0D
confidential, legally=0D
privileged and/or exempt from disclosure pursuant to Massachusetts=0D
law. It is intended=0D
solely for the addressee. If you received this in error, please=0D
contact the sender and=0D
delete the material from any computer.=0D


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Re: Improve ODBC Throughput ?

Obe, Regina a =E9crit :
> What kinds of indexes do you have on your tables? I think the way
> Access works to limit transfer it first pulls the indexes and probably
> the primary one first to determine how to fetch and how many to fetch.
> I suspect if you have no indexes performance would be really bad.=20
>=20
> Also which version of psqlODBC driver are you using?

I have integer primary keys on most tables, if not all of them.
Even my views have an integer primary key (at least,=20
something that can be used as an integer pkey in Access).

My version of psqlODBC is 8.1.2.0.

I forgot to mention something important : I obtain the best=20
performances (500kB / sec, cf my first post) when I am NOT=20
in access.
In a VBA module (Autocad, not Access), I open an ADO=20
connection to PostgreSQL and directly query the server (no=20
linked table involved here). But still, this is "only"=20
500kB/sec...



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: Improve ODBC Throughput ?

--0__=0ABBF89FDFC63C728f9e8a93df938690918c0ABBF89FDFC63C72
Content-type: multipart/alternative;
Boundary="1__=0ABBF89FDFC63C728f9e8a93df938690918c0ABBF89FDF C63C72"

--1__=0ABBF89FDFC63C728f9e8a93df938690918c0ABBF89FDFC63C72
Content-type: text/plain; charset=ISO-8859-1
Content-transfer-encoding: quoted-printable


You say that you have a performance issue.

1. Cache Size
I would guess that the problem may be in how many records you retrieve at a
time.
In VBA code with ADO , it is sometimes helpful to use the Recordset
CacheSize property to pull more than one row at a time.

2.Declare Fetch
In Access, you do not have this fine control. Some psqlODBC user have
experimented with the DECLARE/FETCH option in the psqlODBC settngs.I am not
entirely sure how Declare/Fetch works though, I have never used it.

3. General design for performance
I don't know much about your PostgreSQL database and Access setup, but I
can imagine a situation where you are trying to open a linked table which
has millions of rows and some of the fields are of type TEXT, essentially
BLOBs that Access must treat as MEMO. Simply trying to open something like
that whole is a pretty bad idea for a transactional application. It is
usually best to filter,...retrieve the fewest rows to suit the users need,
and to do other transactions explicitly one records at a time on primary
keys.

Lastly, you could turn on the ODBC trace or the psqlODBC MyLog to looks for
less obvious issues, since you have ruled out the network.

Much Luck to you.

Greg Campbell ENG-ASE/Michelin US5
Lexington, South Carolina
803-951-5561, x75561
Fax: 803-951-5531
greg.campbell@us.michelin.com



=
=20
Arnaud Lesauvage =
=20
=20
.fr> To=
=20
Sent by: "Obe, Regina" =
=20
pgsql-odbc-owner@ =
=20
postgresql.org cc=
=20
pgsql-odbc@postgresql.org =
=20
Subject=
=20
10/19/2006 10:24 Re: [ODBC] Improve ODBC Throughput =
=20
? =
=20
=
=20
=
=20
=
=20
=
=20
=
=20
=
=20




Obe, Regina a =E9crit :
> What kinds of indexes do you have on your tables? I think the way
> Access works to limit transfer it first pulls the indexes and probably
> the primary one first to determine how to fetch and how many to fetch.
> I suspect if you have no indexes performance would be really bad.
>
> Also which version of psqlODBC driver are you using?

I have integer primary keys on most tables, if not all of them.
Even my views have an integer primary key (at least,
something that can be used as an integer pkey in Access).

My version of psqlODBC is 8.1.2.0.

I forgot to mention something important : I obtain the best
performances (500kB / sec, cf my first post) when I am NOT
in access.
In a VBA module (Autocad, not Access), I open an ADO
connection to PostgreSQL and directly query the server (no
linked table involved here). But still, this is "only"
500kB/sec...



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

--1__=0ABBF89FDFC63C728f9e8a93df938690918c0ABBF89FDFC63C72
Content-type: text/html; charset=ISO-8859-1
Content-Disposition: inline
Content-transfer-encoding: quoted-printable

You say that you have a performance issue.



1. Cache Size

I would guess that the problem may be in how many records you retrieve at a=
time.

In VBA code with ADO , it is sometimes helpful to use the Recordset CacheSi=
ze property to pull more than one row at a time.



2.Declare Fetch

In Access, you do not have this fine control. Some psqlODBC user have exper=
imented with the DECLARE/FETCH option in the psqlODBC settngs.I am not enti=
rely sure how Declare/Fetch works though, I have never used it.



3. General design for performance

I don't know much about your PostgreSQL database and Access setup, but I ca=
n imagine a situation where you are trying to open a linked table which has=
millions of rows and some of the fields are of type TEXT, essentially BLOB=
s that Access must treat as MEMO. Simply trying to open something like that=
whole is a pretty bad idea for a transactional application. It is usually =
best to filter,...retrieve the fewest rows to suit the users need, and to d=
o other transactions explicitly one records at a time on primary keys.



Lastly, you could turn on the ODBC trace or the psqlODBC MyLog to looks for=
less obvious issues, since you have ruled out the network.



Much Luck to you.



Greg Campbell ENG-ASE/Michelin US5

Lexington, South Carolina

803-951-5561, x75561

Fax: 803-951-5531

greg.campbell@us.michelin.com



6" height=3D"16" alt=3D"Inactive hide details for Arnaud Lesauvage <thew=
ild@freesurf.fr>">Arnaud Lesauvage <thewild@freesurf.fr>

63C728f9e8a93df9@michelin.com); background-repeat: no-repeat; " width=3D"40= %"> Arnaud Lesauvage <thewild@freesurf.fr>= Sent by: pgsql-odbc-owner@postgresql.org 10/19/2006 10:24

8f9e8a93df9@michelin.com" border=3D"0" height=3D"1" width=3D"58" alt=3D""><= br> To "> =3D"0" height=3D"1" width=3D"1" alt=3D""> "Obe, Regina" <robe.dnd@cityofboston.gov><= /font> 8f9e8a93df9@michelin.com" border=3D"0" height=3D"1" width=3D"58" alt=3D""><= br> cc "> =3D"0" height=3D"1" width=3D"1" alt=3D""> pgsql-odbc@postgresql.org 8f9e8a93df9@michelin.com" border=3D"0" height=3D"1" width=3D"58" alt=3D""><= br> Subject "100%"> der=3D"0" height=3D"1" width=3D"1" alt=3D""> Re: [ODBC] Improve ODBC Throughput ? 8f9e8a93df9@michelin.com" border=3D"0" height=3D"1" width=3D"1" alt=3D""> td> helin.com" border=3D"0" height=3D"1" width=3D"1" alt=3D"">

Re: Improve ODBC Throughput ?

Remember that any data access component is riding on the underlying physica=
l layer.
You need to make sure that your network infrastructure can pump that much d=
ata through it.
Are you network cards running in full duples or half duplex?
What sort of switching infrastructure do you have?
The vast majority of switches have a blocking architecture. In a heavy env=
ironment this will cause oversubscription which will result in congestion. =
If you are using a managed switch, your network admins will hopefully know=
how to troubleshoot and tell you if that is the case (they will see resend=
s, etc.).

Just another area to look at.=20

> -----Original Message-----
> From: pgsql-odbc-owner@postgresql.org=20
> [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Arnaud Lesauvage
> Sent: Thursday, October 19, 2006 8:24 AM
> To: Obe, Regina
> Cc: pgsql-odbc@postgresql.org
> Subject: Re: [ODBC] Improve ODBC Throughput ?
>=20
> Obe, Regina a =E9crit :
> > What kinds of indexes do you have on your tables? I think the way=20
> > Access works to limit transfer it first pulls the indexes=20
> and probably=20
> > the primary one first to determine how to fetch and how=20
> many to fetch.
> > I suspect if you have no indexes performance would be really bad.=20
> >=20
> > Also which version of psqlODBC driver are you using?
>=20
> I have integer primary keys on most tables, if not all of them.
> Even my views have an integer primary key (at least,=20
> something that can be used as an integer pkey in Access).
>=20
> My version of psqlODBC is 8.1.2.0.
>=20
> I forgot to mention something important : I obtain the best=20
> performances (500kB / sec, cf my first post) when I am NOT in access.
> In a VBA module (Autocad, not Access), I open an ADO=20
> connection to PostgreSQL and directly query the server (no=20
> linked table involved here). But still, this is "only"=20
> 500kB/sec...
>=20
>=20
>=20
> ---------------------------(end of=20
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org=20
> so that your
> message can get through to the mailing list cleanly
>=20

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: Improve ODBC Throughput ?

Benjamin Krajmalnik a =E9crit :
> Remember that any data access component is riding on the underlying phy=
sical layer.

Yes, but this network is fast. From the same server, file transfer=20
is using the full ethernet capacity (~10MB / second).


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: Improve ODBC Throughput ?

This is a multi-part message in MIME format.


------_=_NextPart_001_01C6F3A5.D0F7CF1C
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Have you tried one of the systernals tools like ProcessExplorer http:=
//wwwsysinternalscom/Utilities/ProcessExplorerhtml= 0D=0A Or =
FileMonitor http://wwwsysinternalscom/Utilities/F ilemonhtml=
To debug it Those often uncover all sorts of s=
tupid little errors. =0A Thanks, Regina _________=
_______________________ From: Arnaud Lesauvage [mailto:thewild@f=
reesurffr] Sent: Thu 10/19/2006 1:13 PM To: Benjamin Krajmalni=
k Cc: Obe, Regina; pgsql-odbc@postgresqlorg Subject: Re: [ODBC=
] Improve ODBC Throughput ? Benjamin Krajmalnik a =
=E9crit : > Remember that any data access component is riding on the u=
nderlying physical layer. =0AYes, but this network is fast Fro=
m the same server, file transfer is using the full ethernet capacity (=
~10MB / second). =0A-------- -------=
-------------------------- The substance of this message, including an=
y attachments, may be confidential, legally privileged and/or exe=
mpt from disclosure pursuant to Massachusetts law It is intended=0D=
=0Asolely for the addressee If you received this in error, please c=
ontact the sender and delete the material from any computer. =0A
------_=_NextPart_001_01C6F3A5.D0F7CF1C
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

1">=0A=0A=0A=
76381">=0A=0A D>=0A=0A

Re: Improve ODBC Throughput ?

Arnaud Lesauvage wrote:
> Hi list !
>
> I mostly use my PostgreSQL as a backend DB with Access2000 frontends.
> The tables are linked via psqlODBC.
> The problem I have is that even though queries / views run very fast,
> the data take a long time to arrive to the client.
> In the best situations, data is sent/received at ~500kB per second
> (for instance, tables with only one column, which is a very large text
> field >10.000 bytes per field).
>

How are you setting the "Text As LongVarchar" option ?
Are the columns text or memo using Access ?

regards,
Hiroshi Inoue


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Re: Improve ODBC Throughput ?

> In a VBA module (Autocad, not Access), I open an ADO=20
> connection to PostgreSQL and directly query the server (no=20
> linked table involved here).

Sorry if this is slightly off topic from the thread.

I am curious, what functionality are you getting when you connect Autocad=
to Postgresql?

Regards,

Richard Broersma Jr.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Re: Improve ODBC Throughput ?

greg.campbell@us.michelin.com a =E9crit :
> You say that you have a performance issue.
>=20
> 1. Cache Size
> I would guess that the problem may be in how many records you retrieve =
at a
> time.
> In VBA code with ADO , it is sometimes helpful to use the Recordset
> CacheSize property to pull more than one row at a time.

I will try this !
I never used this setting, thansk for pointing this out !

> 2.Declare Fetch
> In Access, you do not have this fine control. Some psqlODBC user have
> experimented with the DECLARE/FETCH option in the psqlODBC settngs.I am=
not
> entirely sure how Declare/Fetch works though, I have never used it.

My tables are linked with these options :
UseDeclareFetch=3D1
UseServerSidePrepare=3D1
Fetch=3D10000
TrueIsMinus1=3D1
BoolsAsChar=3D0
TextAsLongVarchar=3D0
RowVersioning=3D1
RecognizeUniqueIndexes=3D1


> 3. General design for performance
> I don't know much about your PostgreSQL database and Access setup, but =
I
> can imagine a situation where you are trying to open a linked table whi=
ch
> has millions of rows and some of the fields are of type TEXT, essential=
ly
> BLOBs that Access must treat as MEMO. Simply trying to open something l=
ike
> that whole is a pretty bad idea for a transactional application. It is
> usually best to filter,...retrieve the fewest rows to suit the users ne=
ed,
> and to do other transactions explicitly one records at a time on primar=
y
> keys.

All my text fields are maximum 254 characters long (to be=20
recognized as varchar in Access).
I only have very few memo fields, and only when they are=20
compulsorey.
I think the DB design is OK in my case.

> Lastly, you could turn on the ODBC trace or the psqlODBC MyLog to looks=
for
> less obvious issues, since you have ruled out the network.

I'll do that as a last solution, I don't have much time to=20
dig into these huge logs. But I will if I don't find=20
anything else.

> Much Luck to you.

Thanks, I might need luck ! ;)

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: Improve ODBC Throughput ?

Richard Broersma Jr a =E9crit :
>> In a VBA module (Autocad, not Access), I open an ADO=20
>> connection to PostgreSQL and directly query the server (no=20
>> linked table involved here).
>=20
> Sorry if this is slightly off topic from the thread.
>=20
> I am curious, what functionality are you getting when you connect Autoc=
ad to Postgresql?

I retrieve the WKT representation of PostGIS geometries. I=20
wrote some very simple Autocad functions to draw them, and=20
some simple functions to convert Autocad entities to WKT.
Nothing like a connector, but it works quite well
Not perfect yet, I haven't found a simple way to make=20
multipolygons in Autocad. (any Multi obect in fact).

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Re: Improve ODBC Throughput ?

Hiroshi Inoue a =E9crit :
> Arnaud Lesauvage wrote:
>> Hi list !
>>
>> I mostly use my PostgreSQL as a backend DB with Access2000 frontends.
>> The tables are linked via psqlODBC.
>> The problem I have is that even though queries / views run very fast,=20
>> the data take a long time to arrive to the client.
>> In the best situations, data is sent/received at ~500kB per second=20
>> (for instance, tables with only one column, which is a very large text=
=20
>> field >10.000 bytes per field).
>>
>=20
> How are you setting the "Text As LongVarchar" option ?
> Are the columns text or memo using Access ?

TextAsLongVarchar=3D0 but the columns are VarChar in Access.


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: Improve ODBC Throughput ?

> >2.Declare Fetch
> >In Access, you do not have this fine control. Some psqlODBC user have
> >experimented with the DECLARE/FETCH option in the psqlODBC settngs.I am not
> >entirely sure how Declare/Fetch works though, I have never used it.
>
> My tables are linked with these options :
> UseDeclareFetch=1
> UseServerSidePrepare=1
> Fetch=10000

UseDeclareFetch and UseServerSidePrepare are disjoint options.
If I remember it the right way the UseServerSidePrepare disallow
UseDeclareFetch. Please try UseServerSidePrepare=0 when you want
using DeclareFetch.

Why you set Fetch so high? How many rows do you expect?
The UseDeclareFetch declare the select statement as cursor (using
declare statement) and then get the rows using fetch statement
(see postgresql manual).
I see no usage for UseDeclareFetch with so high Fetch.

The UseServerSidePrepare use prepare and execute statement (also
see postgresql manual).

Regards,

Luf

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: Improve ODBC Throughput ?

Arnaud Lesauvage wrote:
> Hiroshi Inoue a =E9crit :
>> Arnaud Lesauvage wrote:
>>> Hi list !
>>>
>>> I mostly use my PostgreSQL as a backend DB with Access2000 frontends.
>>> The tables are linked via psqlODBC.
>>> The problem I have is that even though queries / views run very fast,=
=20
>>> the data take a long time to arrive to the client.
>>> In the best situations, data is sent/received at ~500kB per second=20
>>> (for instance, tables with only one column, which is a very large=20
>>> text field >10.000 bytes per field).
>>>
>>
>> How are you setting the "Text As LongVarchar" option ?
>> Are the columns text or memo using Access ?
>=20
> TextAsLongVarchar=3D0 but the columns are VarChar in Access.

AFAIK the max text(VarChar) size in Access is 255.
Can you get the content of fields > 10000 bytes correctly
under the setting ?

regards,
Hiroshi Inoue


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: Improve ODBC Throughput ?

Hiroshi Inoue a =E9crit :
> Arnaud Lesauvage wrote:
>> Hiroshi Inoue a =E9crit :
>>> Arnaud Lesauvage wrote:
>>>> Hi list !
>>>>
>>>> I mostly use my PostgreSQL as a backend DB with Access2000 frontends=
..
>>>> The tables are linked via psqlODBC.
>>>> The problem I have is that even though queries / views run very fast=
,=20
>>>> the data take a long time to arrive to the client.
>>>> In the best situations, data is sent/received at ~500kB per second=20
>>>> (for instance, tables with only one column, which is a very large=20
>>>> text field >10.000 bytes per field).
>>>>
>>>
>>> How are you setting the "Text As LongVarchar" option ?
>>> Are the columns text or memo using Access ?
>>=20
>> TextAsLongVarchar=3D0 but the columns are VarChar in Access.
>=20
> AFAIK the max text(VarChar) size in Access is 255.
> Can you get the content of fields > 10000 bytes correctly
> under the setting ?

In fact, the only text fields I have are memo in access, but=20
I have very fex text fields. Most of them are character=20
varying of length <=3D 254.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Re: Improve ODBC Throughput ?

Ludek Finstrle a =E9crit :
>> >2.Declare Fetch
>> >In Access, you do not have this fine control. Some psqlODBC user have
>> >experimented with the DECLARE/FETCH option in the psqlODBC settngs.I =
am not
>> >entirely sure how Declare/Fetch works though, I have never used it.
>>=20
>> My tables are linked with these options :
>> UseDeclareFetch=3D1
>> UseServerSidePrepare=3D1
>> Fetch=3D10000
>=20
> UseDeclareFetch and UseServerSidePrepare are disjoint options.
> If I remember it the right way the UseServerSidePrepare disallow
> UseDeclareFetch. Please try UseServerSidePrepare=3D0 when you want
> using DeclareFetch.
>=20
> Why you set Fetch so high? How many rows do you expect?
> The UseDeclareFetch declare the select statement as cursor (using
> declare statement) and then get the rows using fetch statement
> (see postgresql manual).
> I see no usage for UseDeclareFetch with so high Fetch.
>=20
> The UseServerSidePrepare use prepare and execute statement (also
> see postgresql manual).

I thought that since memory consumption was not an issue, I=20
could fetch a lot of rows at once. I read the ODBC=20
documentation about that but not PostgreSQL's, but I'll=20
correct this ASAP.

What is your advice in fact ?

UseDeclareFetch=3D0
UseServerSidePrepare=3D1

Or maybe

UseDeclareFetch=3D1
UseServerSidePrepare=3D0
Fetch=3D100


I might just try both in fact (don't have the time right=20
now, maybe later today though).

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: Improve ODBC Throughput ?

Hello,

> >UseDeclareFetch and UseServerSidePrepare are disjoint options.
> >If I remember it the right way the UseServerSidePrepare disallow
> >UseDeclareFetch. Please try UseServerSidePrepare=0 when you want
> >using DeclareFetch.
> >
> >Why you set Fetch so high? How many rows do you expect?
> >The UseDeclareFetch declare the select statement as cursor (using
> >declare statement) and then get the rows using fetch statement
> >(see postgresql manual).
> >I see no usage for UseDeclareFetch with so high Fetch.
> >
> >The UseServerSidePrepare use prepare and execute statement (also
> >see postgresql manual).
>
> I thought that since memory consumption was not an issue, I
> could fetch a lot of rows at once. I read the ODBC

Yes, it could. When you have UseDeclareFetch=0 it get all rows at once.
But sometimes it take too long when you need few of them ASAP and
others later (so it's good time to use UseDeclareFetch=1).

> documentation about that but not PostgreSQL's, but I'll
> correct this ASAP.

That's no problem. I only point you to the documentation to fully
understand what psqlODBC makes for you internally ;o) It's always
the good start point when you need some tuning.
BTW the start reference point is:
http://www.postgresql.org/docs/8.1/interactive/sql-commands. html

> What is your advice in fact ?

I don't know what you really want. Sometimes is better UseDeclareFetch=1,
sometimes is better UseServerSidePrepare=1 and sometimes is better
UseDeclareFetch=0 and UseServerSidePrepare=0.

I don't know how internally works Access or CAD so I have no advice.

Regards,

Luf

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: Improve ODBC Throughput ?

Arnaud Lesauvage wrote:
> Hiroshi Inoue a =E9crit :
>> Arnaud Lesauvage wrote:
>>> Hiroshi Inoue a =E9crit :
>>>> Arnaud Lesauvage wrote:
>>>>> Hi list !
>>>>>
>>>>> I mostly use my PostgreSQL as a backend DB with Access2000 frontend=
s.
>>>>> The tables are linked via psqlODBC.
>>>>> The problem I have is that even though queries / views run very=20
>>>>> fast, the data take a long time to arrive to the client.
>>>>> In the best situations, data is sent/received at ~500kB per second=20
>>>>> (for instance, tables with only one column, which is a very large=20
>>>>> text field >10.000 bytes per field).
>>>>>
>>>>
>>>> How are you setting the "Text As LongVarchar" option ?
>>>> Are the columns text or memo using Access ?
>>>
>>> TextAsLongVarchar=3D0 but the columns are VarChar in Access.
>>
>> AFAIK the max text(VarChar) size in Access is 255.
>> Can you get the content of fields > 10000 bytes correctly
>> under the setting ?
>=20
> In fact, the only text fields I have are memo in access, but I have ver=
y=20
> fex text fields. Most of them are character varying of length <=3D 254.

What is the type of the field > 10000 bytes in PG ?
If it's the text type, you have to turn on the Text As LongVarchar option=
..

regards,
Hiroshi Inoue

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Re: Improve ODBC Throughput ?

Arnaud Lesauvage wrote:
> Ludek Finstrle a =E9crit :
>>
>> UseDeclareFetch and UseServerSidePrepare are disjoint options.
>> If I remember it the right way the UseServerSidePrepare disallow
>> UseDeclareFetch. Please try UseServerSidePrepare=3D0 when you want
>> using DeclareFetch.

It's not true.
Though the driver doesn't use PREPARE functionality for SELECT statements
under UseDeclareFetch mode, it uses PREPARE functionality for other DML
commands.

regards,
Hiroshi Inoue




---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: Improve ODBC Throughput ?

Hiroshi Inoue a =E9crit :
>> In fact, the only text fields I have are memo in access, but I have ve=
ry=20
>> fex text fields. Most of them are character varying of length <=3D 254=
..
>=20
> What is the type of the field > 10000 bytes in PG ?
> If it's the text type, you have to turn on the Text As LongVarchar opti=
on.

Ok, good point, the type is text and the option was turned off.
I'm changing this right now.
Thanks !

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq